package com.co0964;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.co0964.bean.Category;
import com.co0964.bean.Product;
import com.co0964.util.JdbcUtil;

public class DataAccess {

	public int[] insertCategory(List<Category> categories) throws SQLException{
		int len = categories.size();
		Object[][] params = new Object[len][2];
		for(int i=0; i<len; i++){
			Category category = categories.get(i);
			params[i][0] = category.getCid();
			params[i][1] = category.getCategory();
		}

		String sql = "INSERT INTO category(cid, category) VALUES(?,?)";
		QueryRunner query = new QueryRunner(true);
		return query.batch(JdbcUtil.getConnection(), sql, params);
	}
	
	public int[] insertProduct(List<Product> products) throws SQLException{
		int len = products.size();
		Object[][] params = new Object[len][9];
		for(int i=0; i<len; i++){
			Product product = products.get(i);
			int index = 0;
			params[i][index++] = product.getPid();
			params[i][index++] = product.getPname();
			params[i][index++] = product.getPrice();
			params[i][index++] = product.getWr1();
			params[i][index++] = product.getWr2();
			params[i][index++] = product.getWr3();
			params[i][index++] = product.getWr4();
			params[i][index++] = product.getImg();
			params[i][index++] = product.getCid();
		}

		String sql = "INSERT INTO product(pid, pname, price, wr1, wr2, wr3, wr4, pimg, cid) VALUES(?,?,?,?,?,?,?,?,?)";
		QueryRunner query = new QueryRunner(true);
		return query.batch(JdbcUtil.getConnection(), sql, params);
	}
	
	public List<Category> queryCategory() throws SQLException{
		String sql = "SELECT * FROM category";
		QueryRunner query = new QueryRunner(true);
		List<Category> categories = query.query(JdbcUtil.getConnection(), sql, new BeanListHandler<Category>(Category.class));
		return categories;
	}
	
}
